package com.hsc.db.tools;

import java.util.ArrayList;
import java.util.List;

import com.hsc.account.oneActivity;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class AccountService {


	public  DBOpenHelper dbOpenHelper;
	public SQLiteDatabase  db = null;
	public AccountService(Context context)
	{
		dbOpenHelper = new DBOpenHelper(context);
//		db=dbOpenHelper.getWritableDatabase();
		db = oneActivity.db;
	}

	public void save(AccountList account){
//		SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();

		db.execSQL("insert into acclist(date,time,type,remark,balance,amt,use) values(?,?,?,?,?,?,?)",
				new Object[]{account.getDate(),account.getTime(),account.getType(),account.getRemark(),account.getBalance(),account.getAmt(),account.getUse()});
	}

	//	public void update(AccountList account){
//        SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();
//		
//		db.execSQL("update acclist set date=?,time=? ,type=? ,remark=?,balance=? ,atm=? where id=?",
//				new Object[]{account.getDate(),account.getTime(),account.getType(),account.getRemark(),account.getBalance(),account.getAmt()});
//	}
//	
	public void updateBalance(AccountList account,double flag){
//        SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();

		db.execSQL("update acclist set balance=? where remark=? and date=?",
				new Object[]{flag,account.getRemark(),account.getDate()});
	}

//	public void delete (Integer id){
//		 SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();
//			
//			db.execSQL("delete from acclist where id=?",
//					new Object[]{id});
//	}

	public void delBalance(AccountList account){
//        SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();

		db.execSQL("delete from acclist where remark=? and date=?",
				new Object[]{account.getRemark(),account.getDate()});
	}

	public void deleteByRemark (String uuid){
//		 SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();

		db.execSQL("delete from acclist where remark=?",
				new Object[]{uuid});
	}
//	
//	public AccountList find(Integer id ){
//		SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();
//		Cursor cur = db.rawQuery("select * from acclist where id=?",new String[]{id.toString()});
//		if(cur.moveToFirst())
//		{
//			
//			int aid = cur.getInt(cur.getColumnIndex("id"));
//			String date = cur.getString(cur.getColumnIndex("date"));
//			String time = cur.getString(cur.getColumnIndex("time"));
//			String type = cur.getString(cur.getColumnIndex("type"));
//			String remark = cur.getString(cur.getColumnIndex("remark"));
//			double balance = cur.getFloat(cur.getColumnIndex("blance"));
//			double amt = cur.getFloat(cur.getColumnIndex("amt"));
//			
//            cur.close();			
//			return new AccountList(aid,date,time,type,remark,balance,amt);
//		}
//		return null;
//	}

	public List<AccountList> getListData(int year,int month,int day)
	{
		List<AccountList> list = new ArrayList<AccountList>();
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();
		Cursor cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where date=? and  balance<>0  order by id desc",
				new String[]{String.valueOf(year)+"-"+String.format("%02d", month)+"-"+String.format("%02d", day)});

		while(cur.moveToNext())
		{
			int id = cur.getInt(cur.getColumnIndex("id"));
			String date = cur.getString(cur.getColumnIndex("date"));
			String time = cur.getString(cur.getColumnIndex("time"));
			String type = cur.getString(cur.getColumnIndex("type"));
			String remark = cur.getString(cur.getColumnIndex("remark"));
			String use = cur.getString(cur.getColumnIndex("use"));
			double balance = cur.getDouble(cur.getColumnIndex("balance"));
			double amt = cur.getDouble(cur.getColumnIndex("amt"));
			list.add(new AccountList(id,date,time,type,remark,balance,amt,use));

		}
		cur.close();
		return list;
	}

	public List<AccountList> getListDataByType(int year,int month,String use,int limit,int offset)
	{
		List<AccountList> list = new ArrayList<AccountList>();
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();
		Cursor cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where date>=? and date<=? and use=? and  balance<>0  order by id desc limit ? offset ? ",
				new String[]{ String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"01",String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"31",use,String.valueOf(limit),String.valueOf(offset)});

		while(cur.moveToNext())
		{
			int id = cur.getInt(cur.getColumnIndex("id"));
			String date = cur.getString(cur.getColumnIndex("date"));
			String time = cur.getString(cur.getColumnIndex("time"));
			String type = cur.getString(cur.getColumnIndex("type"));
			String remark = cur.getString(cur.getColumnIndex("remark"));
			String use1 = cur.getString(cur.getColumnIndex("use"));
			double balance = cur.getDouble(cur.getColumnIndex("balance"));
			double amt = cur.getDouble(cur.getColumnIndex("amt"));
			list.add(new AccountList(id,date,time,type,remark,balance,amt,use1));

		}
		cur.close();
		return list;
	}

	public List<AccountList> getListByTypeLike(int year,String typeval,int limit,int offset)
	{
		List<AccountList> list = new ArrayList<AccountList>();
		Cursor cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where date>=? and date<=?  and  balance<>0  and type like ?  order by id desc limit ? offset ? ",
				new String[]{ String.valueOf(year)+"-01-01",String.valueOf(year)+"-12-31","%"+typeval+"%",String.valueOf(limit),String.valueOf(offset)});

		while(cur.moveToNext())
		{
			int id = cur.getInt(cur.getColumnIndex("id"));
			String date = cur.getString(cur.getColumnIndex("date"));
			String time = cur.getString(cur.getColumnIndex("time"));
			String type = cur.getString(cur.getColumnIndex("type"));
			String remark = cur.getString(cur.getColumnIndex("remark"));
			String use1 = cur.getString(cur.getColumnIndex("use"));
			double balance = cur.getDouble(cur.getColumnIndex("balance"));
			double amt = cur.getDouble(cur.getColumnIndex("amt"));
			list.add(new AccountList(id,date,time,type,remark,balance,amt,use1));

		}
		cur.close();
		return list;
	}

	public List<AccountList> getListByAmt(int year,String sAmt,String eAmt,int limit,int offset)
	{
		List<AccountList> list = new ArrayList<AccountList>();


		Cursor cur = null;

		if(sAmt.equals("-") && !eAmt.equals("-"))
		{
			cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where date>=? and date<=?  and  balance<>0  and amt<=cast(? as double) order by id desc limit ? offset ? ",
					new String[]{ String.valueOf(year)+"-01-01",String.valueOf(year)+"-12-31",eAmt,String.valueOf(limit),String.valueOf(offset)});

		}else if(eAmt.equals("-") && !sAmt.equals("-"))
		{
			cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where date>=? and date<=?  and  balance<>0  and amt>=cast(? as double) order by id desc limit ? offset ? ",
					new String[]{ String.valueOf(year)+"-01-01",String.valueOf(year)+"-12-31",sAmt,String.valueOf(limit),String.valueOf(offset)});

		}else if(!eAmt.equals("-") && !sAmt.equals("-"))
		{
			cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where date>=? and date<=?  and  balance<>0  and amt>=cast(? as double) and amt<=cast(? as double) order by id desc limit ? offset ? ",
					new String[]{ String.valueOf(year)+"-01-01",String.valueOf(year)+"-12-31",sAmt,eAmt,String.valueOf(limit),String.valueOf(offset)});

		}



		while(cur.moveToNext())
		{
			int id = cur.getInt(cur.getColumnIndex("id"));
			String date = cur.getString(cur.getColumnIndex("date"));
			String time = cur.getString(cur.getColumnIndex("time"));
			String type = cur.getString(cur.getColumnIndex("type"));
			String remark = cur.getString(cur.getColumnIndex("remark"));
			String use1 = cur.getString(cur.getColumnIndex("use"));
			double balance = cur.getDouble(cur.getColumnIndex("balance"));
			double amt = cur.getDouble(cur.getColumnIndex("amt"));
			list.add(new AccountList(id,date,time,type,remark,balance,amt,use1));

		}
		cur.close();
		return list;
	}

	public List<AccountList> getScroolDataFailed()
	{
		List<AccountList> list = new ArrayList<AccountList>();
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();
		Cursor cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where balance=cast(? as double) or balance=cast(? as double) order by id asc ",
				new String[]{"0","1"});



		while(cur.moveToNext())
		{
			int id = cur.getInt(cur.getColumnIndex("id"));
			String date = cur.getString(cur.getColumnIndex("date"));
			String time = cur.getString(cur.getColumnIndex("time"));
			String type = cur.getString(cur.getColumnIndex("type"));
			String remark = cur.getString(cur.getColumnIndex("remark"));
			String use = cur.getString(cur.getColumnIndex("use"));
			double balance = cur.getDouble(cur.getColumnIndex("balance"));
			double amt = cur.getDouble(cur.getColumnIndex("amt"));
			list.add(new AccountList(id,date,time,type,remark,balance,amt,use));

		}
		cur.close();
		return list;
	}

	public void deleteByMonth (int year,int month){
//		 SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();

		db.execSQL("delete from acclist where date>=? and date <=?",
				new String[]{String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"01",String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"31"});
	}

	public List<AccountList> getScroolDataAdd(int year,int month)
	{
		List<AccountList> list = new ArrayList<AccountList>();
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();
		Cursor cur = db.rawQuery("select id ,date,time,type,remark,balance ,amt,use from acclist where  balance<>cast(? as double) and  date>=? and date <=? order by id asc ",
				new String[]{"0",String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"01",String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"31"});



		while(cur.moveToNext())
		{
			int id = 1;
			String date = cur.getString(cur.getColumnIndex("date"));
			String time = cur.getString(cur.getColumnIndex("time"));
			String type = cur.getString(cur.getColumnIndex("type"));
			String remark = cur.getString(cur.getColumnIndex("remark"));
			String use = cur.getString(cur.getColumnIndex("use"));
			double balance = cur.getDouble(cur.getColumnIndex("balance"));
			double amt = cur.getDouble(cur.getColumnIndex("amt"));
			list.add(new AccountList(id,date,time,type,remark,balance,amt,use));

		}
		cur.close();
		return list;
	}




	public long getCountByRemark(AccountList acc)
	{
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();
		Cursor cur = db.rawQuery("select count(*) from acclist where remark=? and date=?",new String[]{acc.getRemark(),acc.getDate()});
		cur.moveToFirst();
		long count = cur.getLong(0);
		cur.close();
		return count;
	}

//	public long getCountbyDate(int year,int month)
//	{
//		SQLiteDatabase  db = dbOpenHelper.getWritableDatabase();
//		Cursor cur = db.rawQuery("select count(*) from acclist  where date>=? and date <=? ",
//				new String[]{String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"01",String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"31"});
//		cur.moveToFirst();
//		long ret = cur.getLong(0);
//		cur.close();
//		return ret;
//	}

	public double getZSum(String flag,int year,int month)
	{
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();

		Cursor cur=  db.rawQuery("select sum(amt) from acclist where balance<>0 and date>=? and date <=?",new String[]{String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"01",String.valueOf(year)+"-"+String.format("%02d", month)+"-"+"31"});


		cur.moveToFirst();
		double count= cur.getDouble(0);
		cur.close();

		return count;


	}

	public double getYSum(int year)
	{
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();

		Cursor cur=  db.rawQuery("select sum(amt) from acclist where balance<>0 and date>=? and date <=?",new String[]{String.valueOf(year)+"-01-01",String.valueOf(year)+"-12-31"});


		cur.moveToFirst();
		double count= cur.getDouble(0);
		cur.close();

		return count;


	}



	public double getZSumDay(int year,int month,int day)
	{
//		SQLiteDatabase  db = dbOpenHelper.getReadableDatabase();

		Cursor cur=  db.rawQuery("select sum(amt) from acclist where balance<>0  and date=? ",new String[]{String.valueOf(year)+"-"+String.format("%02d", month)+"-"+String.format("%02d", day)});


		cur.moveToFirst();
		double count= cur.getDouble(0);
		cur.close();

		return count;


	}


}
